import pandas as pd
import numpy
import openpyxl
import math


class Sheet:
    def __init__(self, path, filename, sheet, header_line=0):
        self.path = path
        self.filename = filename
        self.sheet = sheet
        self.header_line = header_line
        self.df = pd.read_excel(self.path + self.filename, sheet_name=self.sheet, header=self.header_line)
        self.pyxl_file = openpyxl.load_workbook(self.path + self.filename)
        # xsheet = xfile.get_sheet_by_name('Sheet2')  # 该方法即将弃用
        self.pyxl_sheet = self.pyxl_file[sheet]

    def get_sheets(self):
        '''
        获取excel文件sheet的名字列表
        :return: sheets name str as a list
        '''
        xl = pd.ExcelFile(self.path + self.filename)
        return xl.sheet_names

    def get_col_num(self):
        # 获取列数
        # todo 要改为get_row_num
        return len(self.df)

    def get_max_row(self):
        # 获取最大行数
        return self.pyxl_sheet.max_row

    def get_max_col(self):
        # 获取最大列数
        return self.pyxl_sheet.max_column

    def get_header_value(self):
        header_value = self.df.keys()
        return header_value

    def get_line(self, line_num, header_line=1):
        '''
        返回指定行的数据
        :param line_num: 数据行所在行数，从0开始
        :param header_line: 标题所在行数
        :return:
        '''
        # print('header:   ', df.keys(), type(df.keys()))
        # print(df.values, type(df.keys()))
        content_in_line = self.df.values
        # print(content_in_line[1], content_in_line[1].size,type(content_in_line))  # 获取数据内容(不包括标题行),从0开始
        num = 0
        # print('单元格判断',type(content_in_line[0][3]))
        for content in content_in_line[0]:
            # print(content)
            if content is numpy.nan:  # 判断单元格是否为空
                num += 1
        # print(num)
        # print(f'单元格数量(包含空白格):{df.size}')  # 单元格数量 包含空白单元格
        return content_in_line[line_num]

    def get_nan_count_by_line(self, line_num):
        nan_count = 0
        for value in self.get_line(line_num):
            # print(type(value), value)
            # if value is nan:
            if type(value) is float and math.isnan(value):
                # 如果单元格为空， 则是float类型， 且值为nan，用math.isnan(value)判断
                nan_count += 1
        return nan_count

    def is_all_nan_by_line(self, line_num):
        '''
        判断当前行是否全为空白单元格，数据行第一行为0行
        '''
        for value in self.get_line(line_num):
            if not (type(value) is float and math.isnan(value)):
                return False
        # 如果单元格为空， 则是float类型， 且值为nan，用math.isnan(value)判断
        return True

    def __del__(self):
        pass

    def get_serial_nums(self):
        '''
        获取'序号'列的序号数存入列表中返回
        :param sheet:
        :param header_line:
        :return:
        '''
        # df = pd.read_excel(self.path + self.filename, sheet_name=sheet, header=header_line)
        serial_nums = []
        titles = self.df.keys()
        serial_num = 0  # 序号列标
        # 找到'序号'所在列号
        for i, title in enumerate(titles):
            if '序号' == title:
                serial_num = i
                break
        # 将'序号'列的内容存入列表serial_nums
        for line in self.df.values:
            serial_nums.append(line[serial_num])
        return serial_nums

    def cover_line(self, row_num, row_values):
        '''
        将list中的内容写到指定行中，覆盖以前的数据
        :param row_num:  指定数据行,数据行第一行为0行
        :param row_values: 包含行数据的列表
        :return:
        '''
        # content = self.get_line(row_num)
        col = 'A'  # 起始列号
        for i, value in enumerate(row_values):
            # print(col + str(row_num))
            self.pyxl_sheet[col + str(row_num + 1)] = value
            col = chr(ord(col) + 1)  # 考虑到标题行， +1 是为了让第一行数据行为0行
        try:
            self.pyxl_file.save(self.path + self.filename)
            return 1  # 正常保存
        except Exception as e:
            print(e)
            return -1  # 报错了


if __name__ == '__main__':
    path = './data_source/'
    row_num = 3
    slave_filename = 'panda_read_excel--1.xlsx'
    slave_excel = Sheet(path, slave_filename, sheet='Sheet2')
    master_filename = 'panda_read_excel.xlsx'
    master_excel = Sheet(path, master_filename, sheet='Sheet2')
    print('col_num:', master_excel.get_col_num())
    print('master_line_content', master_excel.get_line(3), 'nan num: ', master_excel.get_nan_count_by_line(3))
    # print('slave_line_content',slave_excel.get_line(13))
    print(master_excel.get_serial_nums())
    print(master_excel.cover_line(row_num, slave_excel.get_line(3)))
    # print(f'标题行是第{master_excel.get_header_line()}行')
    print(slave_excel.get_max_row(),slave_excel.get_max_col())
    print(f'当前行是否全空: {slave_excel.is_all_nan_by_line(7)}')
